import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

public class Manager {
    private Connection connection;
    private static final String BASE_URL = "jdbc:mysql://localhost:3306/";
    private static final String DB_NAME = "student_management";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    public Manager() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection rootConn = DriverManager.getConnection(BASE_URL, USER, PASSWORD);
            recreateDatabase(rootConn);
            rootConn.close();

            connection = DriverManager.getConnection(BASE_URL + DB_NAME, USER, PASSWORD);
            createTable();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void recreateDatabase(Connection rootConn) throws SQLException {
        try (Statement stmt = rootConn.createStatement();
             ResultSet rs = rootConn.getMetaData().getCatalogs()) {
            boolean dbExists = false;
            while (rs.next()) {
                if (DB_NAME.equalsIgnoreCase(rs.getString(1))) {
                    dbExists = true;
                    break;
                }
            }

            if (dbExists) {
                stmt.executeUpdate("DROP DATABASE " + DB_NAME);
                System.out.println("Database dropped: " + DB_NAME);
            }

            stmt.executeUpdate("CREATE DATABASE " + DB_NAME);
            System.out.println("Database created: " + DB_NAME);
        }
    }

    private void createTable() throws SQLException {
        String sql = "CREATE TABLE students (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "student_id VARCHAR(20) NOT NULL UNIQUE," +
                "name VARCHAR(50) NOT NULL," +
                "age INT NOT NULL," +
                "class_name VARCHAR(50) NOT NULL," +
                "dormitory VARCHAR(50) NOT NULL," +
                "is_dorm_leader BOOLEAN NOT NULL" +
                ")";
        try (Statement stmt = connection.createStatement()) {
            stmt.execute(sql);
            System.out.println("Table created: students");
        }
    }

    private boolean hasDormLeader(String dormitory) {
        String sql = "SELECT COUNT(*) FROM students WHERE dormitory = ? AND is_dorm_leader = true";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, dormitory);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            System.err.println("Error checking dorm leader: " + e.getMessage());
        }
        return false;
    }

    public boolean addStudent(Students stu) {
        if (stu.isDormLeader() && hasDormLeader(stu.getDormitory())) {
            System.out.println("该宿舍已存在宿舍长，无法添加新的宿舍长。");
            return false;
        }
        String sql = "INSERT INTO students (student_id, name, age, class_name, dormitory, is_dorm_leader) " +
                "VALUES (?, ?, ?, ?, ?, ?)";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, stu.getStudentId());
            pstmt.setString(2, stu.getName());
            pstmt.setInt(3, stu.getAge());
            pstmt.setString(4, stu.getClassName());
            pstmt.setString(5, stu.getDormitory());
            pstmt.setBoolean(6, stu.isDormLeader());
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            System.err.println("Error adding student: " + e.getMessage());
            return false;
        }
    }

    public boolean deleteStudent(String studentId) {
        String sql = "DELETE FROM students WHERE student_id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, studentId);
            int affectedRows = pstmt.executeUpdate();
            System.out.println("Query OK, " + affectedRows + " row affected");
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println("Error deleting student: " + e.getMessage());
            return false;
        }
    }

    public boolean updateStudent(String studentId, Students newStudent) {
        if (newStudent.isDormLeader() && hasDormLeader(newStudent.getDormitory())) {
            Students oldStudent = findStudentById(studentId);
            if (oldStudent == null || !oldStudent.isDormLeader() ||
                    !oldStudent.getDormitory().equals(newStudent.getDormitory())) {
                System.out.println("该宿舍已存在宿舍长，无法更新为新的宿舍长。");
                return false;
            }
        }
        String sql = "UPDATE students SET student_id = ?, name = ?, age = ?, class_name = ?, " +
                "dormitory = ?, is_dorm_leader = ? WHERE student_id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, newStudent.getStudentId());
            pstmt.setString(2, newStudent.getName());
            pstmt.setInt(3, newStudent.getAge());
            pstmt.setString(4, newStudent.getClassName());
            pstmt.setString(5, newStudent.getDormitory());
            pstmt.setBoolean(6, newStudent.isDormLeader());
            pstmt.setString(7, studentId);
            int affectedRows = pstmt.executeUpdate();
            System.out.println("Query OK, " + affectedRows + " row affected");
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println("Error updating student: " + e.getMessage());
            return false;
        }
    }

    public Students findStudentById(String studentId) {
        String sql = "SELECT * FROM students WHERE student_id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, studentId);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return new Students(
                        rs.getString("student_id"),
                        rs.getString("name"),
                        rs.getInt("age"),
                        rs.getString("class_name"),
                        rs.getString("dormitory"),
                        rs.getBoolean("is_dorm_leader")
                );
            }
        } catch (SQLException e) {
            System.err.println("Error finding student: " + e.getMessage());
        }
        return null;
    }

    public List<Students> findStudentsByName(String name) {
        List<Students> students = new ArrayList<>();
        String sql = "SELECT * FROM students WHERE name LIKE ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, "%" + name + "%");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                students.add(new Students(
                        rs.getString("student_id"),
                        rs.getString("name"),
                        rs.getInt("age"),
                        rs.getString("class_name"),
                        rs.getString("dormitory"),
                        rs.getBoolean("is_dorm_leader")
                ));
            }
        } catch (SQLException e) {
            System.err.println("Error finding students by name: " + e.getMessage());
        }
        return students;
    }

    public List<Students> getAllStudents(String orderBy, String orderField) {
        List<Students> students = new ArrayList<>();
        String validField = "student_id".equals(orderField) || "name".equals(orderField)
                || "age".equals(orderField) || "class_name".equals(orderField)
                || "dormitory".equals(orderField) ? orderField : "student_id";
        String sql = "SELECT * FROM students ORDER BY " + validField + " " + ("DESC".equals(orderBy) ? "DESC" : "ASC");

        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                students.add(new Students(
                        rs.getString("student_id"),
                        rs.getString("name"),
                        rs.getInt("age"),
                        rs.getString("class_name"),
                        rs.getString("dormitory"),
                        rs.getBoolean("is_dorm_leader")
                ));
            }
        } catch (SQLException e) {
            System.err.println("Error getting all students: " + e.getMessage());
        }
        return students;
    }

    public List<Students> findStudentsByDormitory(String dormitory) {
        return getAllStudents("ASC", "student_id").stream()
                .filter(s -> s.getDormitory().equals(dormitory))
                .collect(Collectors.toList());
    }

    public List<Students> findDormLeaders() {
        return getAllStudents("ASC", "student_id").stream()
                .filter(Students::isDormLeader)
                .collect(Collectors.toList());
    }

    public void showDatabaseView(List<Students> students) {
        if (students.isEmpty()) {
            System.out.println("Empty set (0.00 sec)\n");
            return;
        }

        // 计算每列的最大宽度
        int idWidth = "student_id".length();
        int nameWidth = "name".length();
        int ageWidth = "age".length();
        int classWidth = "class_name".length();
        int dormWidth = "dormitory".length();
        int leaderWidth = "is_dorm_leader".length();

        for (Students stu : students) {
            idWidth = Math.max(idWidth, stu.getStudentId().length());
            nameWidth = Math.max(nameWidth, stu.getName().length());
            ageWidth = Math.max(ageWidth, String.valueOf(stu.getAge()).length());
            classWidth = Math.max(classWidth, stu.getClassName().length());
            dormWidth = Math.max(dormWidth, stu.getDormitory().length());
            leaderWidth = Math.max(leaderWidth, (stu.isDormLeader() ? "YES" : "NO").length());
        }

        // 打印表头
        printMySQLTableHeader(idWidth, nameWidth, ageWidth, classWidth, dormWidth, leaderWidth);

        // 打印数据行
        for (Students stu : students) {
            printMySQLTableRow(stu, idWidth, nameWidth, ageWidth, classWidth, dormWidth, leaderWidth);
        }

        // 打印表尾
        printMySQLTableFooter(idWidth, nameWidth, ageWidth, classWidth, dormWidth, leaderWidth);

        // 打印MySQL风格的统计信息
        System.out.println(students.size() + " rows in set (0.00 sec)\n");
    }

    private void printMySQLTableHeader(int idW, int nameW, int ageW, int classW, int dormW, int leaderW) {
        // 顶部边框
        System.out.print("+");
        printDashedLine(idW + 2);
        System.out.print("+");
        printDashedLine(nameW + 2);
        System.out.print("+");
        printDashedLine(ageW + 2);
        System.out.print("+");
        printDashedLine(classW + 2);
        System.out.print("+");
        printDashedLine(dormW + 2);
        System.out.print("+");
        printDashedLine(leaderW + 2);
        System.out.println("+");

        // 列名行
        System.out.printf("| %-" + idW + "s | %-" + nameW + "s | %" + ageW + "s | %-" +
                        classW + "s | %-" + dormW + "s | %-" + leaderW + "s |\n",
                "student_id", "name", "age", "class_name", "dormitory", "is_dorm_leader");

        // 分隔线
        System.out.print("+");
        printDashedLine(idW + 2);
        System.out.print("+");
        printDashedLine(nameW + 2);
        System.out.print("+");
        printDashedLine(ageW + 2);
        System.out.print("+");
        printDashedLine(classW + 2);
        System.out.print("+");
        printDashedLine(dormW + 2);
        System.out.print("+");
        printDashedLine(leaderW + 2);
        System.out.println("+");
    }

    private void printMySQLTableRow(Students stu, int idW, int nameW, int ageW, int classW, int dormW, int leaderW) {
        System.out.printf("| %-" + idW + "s | %-" + nameW + "s | %" + ageW + "d | %-" +
                        classW + "s | %-" + dormW + "s | %-" + leaderW + "s |\n",
                stu.getStudentId(),
                stu.getName(),
                stu.getAge(),
                stu.getClassName(),
                stu.getDormitory(),
                stu.isDormLeader() ? "YES" : "NO");
    }

    private void printMySQLTableFooter(int idW, int nameW, int ageW, int classW, int dormW, int leaderW) {
        System.out.print("+");
        printDashedLine(idW + 2);
        System.out.print("+");
        printDashedLine(nameW + 2);
        System.out.print("+");
        printDashedLine(ageW + 2);
        System.out.print("+");
        printDashedLine(classW + 2);
        System.out.print("+");
        printDashedLine(dormW + 2);
        System.out.print("+");
        printDashedLine(leaderW + 2);
        System.out.println("+");
    }

    private void printDashedLine(int length) {
        System.out.print(new String(new char[length]).replace('\0', '-'));
    }

    public void close() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
                System.out.println("Connection closed");
            }
        } catch (SQLException e) {
            System.err.println("Error closing connection: " + e.getMessage());
        }
    }
}